﻿<%@ WebHandler Language="C#" Class="ManagerJSONHandler" %>

using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using Newtonsoft.Json;
public class ManagerJSONHandler : IHttpHandler {
    
    public void ProcessRequest (HttpContext context) {
        string strconn = UserClass.strconn;/*App_Code->UserClass.cs*/
        string strcmd = "";
        string type = context.Request.Form["type"];
        using (SqlConnection conn = new SqlConnection(strconn))
        {
            switch (type)
            {
                case "1":
                    strcmd = @"select 
                               DATEPART(YYYY,[user_registration_date])as year 
                              ,DATEPART(MONTH,[user_registration_date]) as month
                              ,count(DATEPART(MONTH,[user_registration_date])) as PeopleNum
                              from [UBOX].[dbo].[user]
                              where @Start<=DATEPART(YYYY,[user_registration_date]) and @End>=DATEPART(YYYY,[user_registration_date])
                              group by DATEPART(MONTH,[user_registration_date]),DATEPART(YYYY,[user_registration_date]) 
                              order by DATEPART(YYYY,[user_registration_date]),DATEPART(MONTH,[user_registration_date])
                            ";
                    break;
                case "2":
                    strcmd = @"select 
                               max(DATEPART(YYYY,[user_registration_date])) as EndYear
                               ,min(DATEPART(YYYY,[user_registration_date])) as StartYear
                              from [UBOX].[dbo].[user]  
                            ";
                    break;
                case "3":
                    strcmd = @"select 
                               DATEPART(YYYY,[user_birthday])as year
							   ,[user_sex] as sex
                              ,count(DATEPART(MONTH,[user_birthday])) as PeopleNum
                              from [UBOX].[dbo].[user]
                              where DATEPART(YYYY,[user_birthday])>@Time and @Start<=DATEPART(YYYY,[user_registration_date]) and @End>=DATEPART(YYYY,[user_registration_date])
                              group by DATEPART(YYYY,[user_birthday]),[user_sex]
                              order by DATEPART(YYYY,[user_birthday]) DESC
                            ";
                    break;
            }
            using (SqlDataAdapter da = new SqlDataAdapter(strcmd, conn))
            {
                SqlParameter Result;
                Result = new SqlParameter("@Result", SqlDbType.Bit);
                Result.Direction = ParameterDirection.Output;
                switch (type)
                {
                    case "1":
                        //input
                        da.SelectCommand.Parameters.AddWithValue("@Start", context.Request.Form["start"]);
                        da.SelectCommand.Parameters.AddWithValue("@End", context.Request.Form["end"]);
                        //output
                        break;
                    case "3":
                        //input
                        da.SelectCommand.Parameters.AddWithValue("@Start",context.Request.Form["start"] );
                        da.SelectCommand.Parameters.AddWithValue("@End", context.Request.Form["end"]);
                        /*100年內*/
                        da.SelectCommand.Parameters.AddWithValue("@Time", DateTime.Now.AddYears(-100).Year);
                        //output
                        break;
                }
                DataSet ds = new DataSet();
                da.Fill(ds, "data");
                context.Response.ContentType = "application/json";
                context.Response.Write(JsonConvert.SerializeObject(ds));
                conn.Close();
            }
        }
    }
    public bool IsReusable {
        get {
            return false;
        }
    }

}